{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "initial_id",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:00:30.427331800Z",
     "start_time": "2023-11-11T02:00:30.416125700Z"
    },
    "jupyter": {
     "outputs_hidden": true
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "import numpy as np \n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns \n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "from tqdm import tqdm "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4a3d80b23d7ed94",
   "metadata": {},
   "source": [
    "# 大目标\n",
    "- 对数据进行统计分析\n",
    "- 数据可视化\n",
    "\n",
    "# 流程说明\n",
    "- 加工流程按先后顺序分为以下几个工序：**扫描、图像处理、自检全检、PDF处理。**\n",
    "- 操作人员首先批量领取一定数量的任务\n",
    "- dUPDATE_TIME：每份案卷的领取时间\n",
    "- dNODE_TIME：每份案卷的提交时间\n",
    "- 中午休息前和下班前提交已完成的案卷\n",
    "- 允许操作人员在未完成已领取的任务前领取新任务\n",
    "- 工作效率按批次算，**总耗时**： 同一批案卷的最后提交时间-最早领取时间\n",
    "- sBatch_number：批的编号\n",
    "- iNODE_STATUS（工序状态）：2，已完成提交且不用返工。 5，已完成提交，经过反工\n",
    "- 工作时间：周一至周六上午8:30-12:00，下午13:00-18:00\n",
    "- 案卷处理时间和操作人员的工作时长需去掉非工作时间"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e8bc8a55f127b32",
   "metadata": {},
   "source": [
    "# 数据说明\n",
    "\n",
    "- iID ： 记录ID（主键）\n",
    "- iPID：主ID\n",
    "- uFILE_FLAG ：案卷标识（GUID）\n",
    "- sARCH_ID ： 案卷号  \n",
    "- sFLOW_NAME： 工作流名称\n",
    "- sNODE_NAME ：工序节点名称\n",
    "- iNODE_STATUS ：工序状态\n",
    "    - 2：案卷已完成，且已提交； \n",
    "    - 5：案卷需要返工，且已提交 \n",
    "- iUSER_ID :操作人员ID \n",
    "- iWF_ID :工作流ID \n",
    "- iWN_ID :流程节点ID \n",
    "    - 12：扫描；13：图像处理；22：自检全检；15：PDF 处理 \n",
    "- sPIC_PATH ：图片路径  \n",
    "- iFLOW_NODE_NO ：工序号 \n",
    "    - 1：扫描；2：图像处理；3：自检全检；4：PDF 处理 \n",
    "- iPROC_USERID：返工操作人员ID  \n",
    "- sPIC_SERVER_PATH ： 图片路径  \n",
    "- sPDF_SERVER_PATH ： PDF 路径  \n",
    "- iARCH_TYPE ：案卷类型\n",
    "- sORDER_ARCH_ID ：排序档号 \n",
    "- dUPDATE_TIME ：工序开始时间，该工序节点开始时间\n",
    "- dNODE_TIME ：工序结束时间，该工序节点结束时间 \n",
    "- dPROC_TIME ：返工时间，返工开始时间\n",
    "- sBatch_number ：批次编号"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7fbfdfed30e2136",
   "metadata": {},
   "source": [
    "# Task1.1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ec43e7eb78a3a142",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:01:04.487130400Z",
     "start_time": "2023-11-11T02:00:34.473006100Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>iID</th>\n",
       "      <th>iPID</th>\n",
       "      <th>uFILE_FLAG</th>\n",
       "      <th>sARCH_ID</th>\n",
       "      <th>sFLOW_NAME</th>\n",
       "      <th>sNODE_NAME</th>\n",
       "      <th>iNODE_STATUS</th>\n",
       "      <th>iUSER_ID</th>\n",
       "      <th>iWF_ID</th>\n",
       "      <th>iWN_ID</th>\n",
       "      <th>...</th>\n",
       "      <th>iFLOW_NODE_NO</th>\n",
       "      <th>iPROC_USERID</th>\n",
       "      <th>sPIC_SERVER_PATH</th>\n",
       "      <th>sPDF_SERVER_PATH</th>\n",
       "      <th>iARCH_TYPE</th>\n",
       "      <th>sORDER_ARCH_ID</th>\n",
       "      <th>dUPDATE_TIME</th>\n",
       "      <th>dNODE_TIME</th>\n",
       "      <th>dPROC_TIME</th>\n",
       "      <th>sBatch_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>725975</td>\n",
       "      <td>186250</td>\n",
       "      <td>0B71F6DD-E7EC-4FB2-87C4-EB576B70F4C2</td>\n",
       "      <td>托644031-册一</td>\n",
       "      <td>不动产工作流</td>\n",
       "      <td>扫描</td>\n",
       "      <td>2</td>\n",
       "      <td>68</td>\n",
       "      <td>20</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\\\127.0.0.1\\tlscan\\tlscanfile</td>\n",
       "      <td>\\\\192.168.1.253\\tlscan\\tlscanpdf</td>\n",
       "      <td>91</td>\n",
       "      <td>托00644031-册001</td>\n",
       "      <td>2020-07-01 09:17:22.313</td>\n",
       "      <td>2020-07-01 09:21:10</td>\n",
       "      <td>NaT</td>\n",
       "      <td>902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>725976</td>\n",
       "      <td>186251</td>\n",
       "      <td>7B0B1758-12F7-4A44-91F9-96C5B9B2E6FD</td>\n",
       "      <td>托644032-册一</td>\n",
       "      <td>不动产工作流</td>\n",
       "      <td>扫描</td>\n",
       "      <td>2</td>\n",
       "      <td>68</td>\n",
       "      <td>20</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\\\127.0.0.1\\tlscan\\tlscanfile</td>\n",
       "      <td>\\\\192.168.1.253\\tlscan\\tlscanpdf</td>\n",
       "      <td>91</td>\n",
       "      <td>托00644032-册001</td>\n",
       "      <td>2020-07-01 09:17:42.867</td>\n",
       "      <td>2020-07-01 09:21:17</td>\n",
       "      <td>NaT</td>\n",
       "      <td>902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>725249</td>\n",
       "      <td>185781</td>\n",
       "      <td>94CC72C0-A6B4-4627-868E-EE3B423877C7</td>\n",
       "      <td>托7181-册一</td>\n",
       "      <td>不动产工作流</td>\n",
       "      <td>扫描</td>\n",
       "      <td>2</td>\n",
       "      <td>44</td>\n",
       "      <td>20</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\\\127.0.0.1\\tlscan\\tlscanfile</td>\n",
       "      <td>\\\\192.168.1.253\\tlscan\\tlscanpdf</td>\n",
       "      <td>91</td>\n",
       "      <td>托00007181-册001</td>\n",
       "      <td>2020-07-01 08:39:08.370</td>\n",
       "      <td>2020-07-01 09:26:29</td>\n",
       "      <td>NaT</td>\n",
       "      <td>604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>725250</td>\n",
       "      <td>185782</td>\n",
       "      <td>FA6E9CC1-5579-41D5-918B-6B9F0D3B2486</td>\n",
       "      <td>托7182-册一</td>\n",
       "      <td>不动产工作流</td>\n",
       "      <td>扫描</td>\n",
       "      <td>2</td>\n",
       "      <td>44</td>\n",
       "      <td>20</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\\\127.0.0.1\\tlscan\\tlscanfile</td>\n",
       "      <td>\\\\192.168.1.253\\tlscan\\tlscanpdf</td>\n",
       "      <td>91</td>\n",
       "      <td>托00007182-册001</td>\n",
       "      <td>2020-07-01 08:39:08.397</td>\n",
       "      <td>2020-07-01 09:26:36</td>\n",
       "      <td>NaT</td>\n",
       "      <td>604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>725251</td>\n",
       "      <td>185783</td>\n",
       "      <td>01016875-EA2C-43BE-9637-CD649F21E58A</td>\n",
       "      <td>托7183-册一</td>\n",
       "      <td>不动产工作流</td>\n",
       "      <td>扫描</td>\n",
       "      <td>2</td>\n",
       "      <td>44</td>\n",
       "      <td>20</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\\\\127.0.0.1\\tlscan\\tlscanfile</td>\n",
       "      <td>\\\\192.168.1.253\\tlscan\\tlscanpdf</td>\n",
       "      <td>91</td>\n",
       "      <td>托00007183-册001</td>\n",
       "      <td>2020-07-01 08:39:08.420</td>\n",
       "      <td>2020-07-01 09:26:42</td>\n",
       "      <td>NaT</td>\n",
       "      <td>604</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      iID    iPID                            uFILE_FLAG    sARCH_ID  \\\n",
       "0  725975  186250  0B71F6DD-E7EC-4FB2-87C4-EB576B70F4C2  托644031-册一   \n",
       "1  725976  186251  7B0B1758-12F7-4A44-91F9-96C5B9B2E6FD  托644032-册一   \n",
       "2  725249  185781  94CC72C0-A6B4-4627-868E-EE3B423877C7    托7181-册一   \n",
       "3  725250  185782  FA6E9CC1-5579-41D5-918B-6B9F0D3B2486    托7182-册一   \n",
       "4  725251  185783  01016875-EA2C-43BE-9637-CD649F21E58A    托7183-册一   \n",
       "\n",
       "  sFLOW_NAME sNODE_NAME  iNODE_STATUS  iUSER_ID  iWF_ID  iWN_ID  ...  \\\n",
       "0     不动产工作流         扫描             2        68      20      12  ...   \n",
       "1     不动产工作流         扫描             2        68      20      12  ...   \n",
       "2     不动产工作流         扫描             2        44      20      12  ...   \n",
       "3     不动产工作流         扫描             2        44      20      12  ...   \n",
       "4     不动产工作流         扫描             2        44      20      12  ...   \n",
       "\n",
       "  iFLOW_NODE_NO  iPROC_USERID               sPIC_SERVER_PATH  \\\n",
       "0             1           NaN  \\\\127.0.0.1\\tlscan\\tlscanfile   \n",
       "1             1           NaN  \\\\127.0.0.1\\tlscan\\tlscanfile   \n",
       "2             1           NaN  \\\\127.0.0.1\\tlscan\\tlscanfile   \n",
       "3             1           NaN  \\\\127.0.0.1\\tlscan\\tlscanfile   \n",
       "4             1           NaN  \\\\127.0.0.1\\tlscan\\tlscanfile   \n",
       "\n",
       "                   sPDF_SERVER_PATH iARCH_TYPE  sORDER_ARCH_ID  \\\n",
       "0  \\\\192.168.1.253\\tlscan\\tlscanpdf         91  托00644031-册001   \n",
       "1  \\\\192.168.1.253\\tlscan\\tlscanpdf         91  托00644032-册001   \n",
       "2  \\\\192.168.1.253\\tlscan\\tlscanpdf         91  托00007181-册001   \n",
       "3  \\\\192.168.1.253\\tlscan\\tlscanpdf         91  托00007182-册001   \n",
       "4  \\\\192.168.1.253\\tlscan\\tlscanpdf         91  托00007183-册001   \n",
       "\n",
       "             dUPDATE_TIME          dNODE_TIME dPROC_TIME sBatch_number  \n",
       "0 2020-07-01 09:17:22.313 2020-07-01 09:21:10        NaT           902  \n",
       "1 2020-07-01 09:17:42.867 2020-07-01 09:21:17        NaT           902  \n",
       "2 2020-07-01 08:39:08.370 2020-07-01 09:26:29        NaT           604  \n",
       "3 2020-07-01 08:39:08.397 2020-07-01 09:26:36        NaT           604  \n",
       "4 2020-07-01 08:39:08.420 2020-07-01 09:26:42        NaT           604  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Task1.1\n",
    "\n",
    "df = pd.read_excel('D:\\A题-档案数字化加工流程数据分析\\\\data.xlsx')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "864b9b4a7418186b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:01:04.604766900Z",
     "start_time": "2023-11-11T02:01:04.489134300Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "## 时间归类\n",
    "scanned = df[df['sNODE_NAME'].eq('扫描')][['sARCH_ID', 'dUPDATE_TIME', 'dNODE_TIME']].rename(columns={'dUPDATE_TIME': '扫描开始时间', 'dNODE_TIME': '扫描结束时间'})\n",
    "\n",
    "image_processed = df[df['sNODE_NAME'].eq('图像处理')][['sARCH_ID', 'dUPDATE_TIME', 'dNODE_TIME']].rename(columns={'dUPDATE_TIME': '图像处理开始时间', 'dNODE_TIME': '图像处理结束时间'})\n",
    "\n",
    "self_checked = df[df['sNODE_NAME'].eq('自检全检')][['sARCH_ID', 'dUPDATE_TIME', 'dNODE_TIME']].rename(columns={'dUPDATE_TIME': '自检全检开始时间', 'dNODE_TIME': '自检全检结束时间'})\n",
    "\n",
    "pdf_processed = df[df['sNODE_NAME'].eq('PDF处理')][['sARCH_ID', 'dUPDATE_TIME', 'dNODE_TIME']].rename(columns={'dUPDATE_TIME': 'PDF处理开始时间', 'dNODE_TIME': 'PDF处理结束时间'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "727c998be1871118",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:01:04.685758600Z",
     "start_time": "2023-11-11T02:01:04.602759300Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "## 数据合并\n",
    "df_final = pd.merge(scanned, image_processed, on='sARCH_ID', how='outer')\n",
    "df_final = pd.merge(df_final, self_checked, on='sARCH_ID', how='outer')\n",
    "df_final = pd.merge(df_final, pdf_processed, on='sARCH_ID', how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3b8c31060d5ef4ad",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:03:32.604604Z",
     "start_time": "2023-11-11T02:03:32.571605100Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 33980 entries, 0 to 33984\n",
      "Data columns (total 9 columns):\n",
      " #   Column     Non-Null Count  Dtype         \n",
      "---  ------     --------------  -----         \n",
      " 0   sARCH_ID   33980 non-null  object        \n",
      " 1   扫描开始时间     33980 non-null  datetime64[ns]\n",
      " 2   扫描结束时间     33980 non-null  datetime64[ns]\n",
      " 3   图像处理开始时间   33980 non-null  datetime64[ns]\n",
      " 4   图像处理结束时间   33980 non-null  datetime64[ns]\n",
      " 5   自检全检开始时间   33980 non-null  datetime64[ns]\n",
      " 6   自检全检结束时间   33980 non-null  datetime64[ns]\n",
      " 7   PDF处理开始时间  33980 non-null  datetime64[ns]\n",
      " 8   PDF处理结束时间  33980 non-null  datetime64[ns]\n",
      "dtypes: datetime64[ns](8), object(1)\n",
      "memory usage: 2.6+ MB\n"
     ]
    }
   ],
   "source": [
    "df_final.dropna().info()  # 时间里面存在缺失值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "17a10129232221dc",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:01:39.649969400Z",
     "start_time": "2023-11-11T02:01:39.643959Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 定义时间范围\n",
    "import datetime\n",
    "work_start_time_AM = datetime.datetime.combine(datetime.date.today(), datetime.time(8, 30))  # 8:30 AM    \n",
    "work_end_time_AM = datetime.datetime.combine(datetime.date.today(), datetime.time(12, 0)) # 12:00 AM  \n",
    "work_start_time_PM = datetime.datetime.combine(datetime.date.today(), datetime.time(13, 0))  # 13:00 PM    \n",
    "work_end_time_PM = datetime.datetime.combine(datetime.date.today(), datetime.time(18, 0))  # 6:00 PM  \n",
    "\n",
    "\n",
    "def adjust_time(row):\n",
    "            \n",
    "\n",
    "    start_dates = [pd.to_datetime(time).date() for time in [row['扫描开始时间'], row['图像处理开始时间'], row['自检全检开始时间'], row['PDF处理开始时间']]]\n",
    "    end_dates = [pd.to_datetime(time).date() for time in [row['扫描结束时间'], row['图像处理结束时间'], row['自检全检结束时间'], row['PDF处理结束时间']]]\n",
    "    start_times = [pd.to_datetime(time).time() for time in [row['扫描开始时间'], row['图像处理开始时间'], row['自检全检开始时间'], row['PDF处理开始时间']]]\n",
    "    end_times = [pd.to_datetime(time).time() for time in [row['扫描结束时间'], row['图像处理结束时间'], row['自检全检结束时间'], row['PDF处理结束时间']]]\n",
    "    \n",
    "\n",
    "    early_start_count = sum((time < work_start_time_AM.time()) or ((time < work_start_time_PM.time()) & (time > work_end_time_AM.time())) for time in start_times if pd.notnull(time))\n",
    "    late_end_count = sum(((time < work_start_time_PM.time()) & (time > work_end_time_AM.time())) or (time>work_end_time_PM.time()) for time in end_times if pd.notnull(time))\n",
    "\n",
    "    for i, (start, end) in enumerate(zip(start_times, end_times)):\n",
    "        if pd.notnull(start) and (start < work_start_time_AM.time()):\n",
    "            start_times[i] = work_start_time_AM.time()\n",
    "        if pd.notnull(start) and ((start < work_start_time_PM.time()) & (start > work_end_time_AM.time())):\n",
    "            start_times[i] = work_start_time_PM.time()\n",
    "\n",
    "        if pd.notnull(end) and ((end < work_start_time_PM.time()) & (end > work_end_time_AM.time())):\n",
    "            end_times[i] = work_end_time_AM.time()\n",
    "        if pd.notnull(end) and (end>work_end_time_PM.time()):\n",
    "            end_times[i] = work_end_time_PM.time()\n",
    "\n",
    "    row['扫描开始时间'] = datetime.datetime.combine(start_dates[0], start_times[0])\n",
    "    row['图像处理开始时间'] = datetime.datetime.combine(start_dates[1], start_times[1])\n",
    "    row['自检全检开始时间'] = datetime.datetime.combine(start_dates[2], start_times[2])\n",
    "    row['PDF处理开始时间'] = datetime.datetime.combine(start_dates[3], start_times[3])\n",
    "\n",
    "\n",
    "    row['扫描结束时间'] = datetime.datetime.combine(end_dates[0], end_times[0])\n",
    "    row['图像处理结束时间'] = datetime.datetime.combine(end_dates[1], end_times[1])\n",
    "    row['自检全检结束时间'] = datetime.datetime.combine(end_dates[2], end_times[2])\n",
    "    row['PDF处理结束时间'] = datetime.datetime.combine(end_dates[3], end_times[3])\n",
    "\n",
    "\n",
    "    return early_start_count > 0, late_end_count > 0, row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d7388cc1db3a1cbb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:03:56.573576600Z",
     "start_time": "2023-11-11T02:03:50.596132500Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "存在提前上岗的案卷数量：8561\n",
      "存在推迟下岗的案卷数量：6117\n"
     ]
    }
   ],
   "source": [
    "early_start = []\n",
    "late_end = []\n",
    "df_final = df_final.dropna()\n",
    "for _, row in df_final.iterrows():\n",
    "    is_early, is_late, row = adjust_time(row)\n",
    "    early_start.append(is_early)\n",
    "    late_end.append(is_late)\n",
    "\n",
    "df_final['存在提前上岗'] = early_start\n",
    "df_final['存在推迟下岗'] = late_end\n",
    "\n",
    "early_count = sum(df_final['存在提前上岗'])\n",
    "late_count = sum(df_final['存在推迟下岗'])\n",
    "print(f\"存在提前上岗的案卷数量：{early_count}\")\n",
    "print(f\"存在推迟下岗的案卷数量：{late_count}\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a0e17f201d73eb3b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:05:52.978115900Z",
     "start_time": "2023-11-11T02:05:52.962656Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sARCH_ID</th>\n",
       "      <th>扫描开始时间</th>\n",
       "      <th>扫描结束时间</th>\n",
       "      <th>图像处理开始时间</th>\n",
       "      <th>图像处理结束时间</th>\n",
       "      <th>自检全检开始时间</th>\n",
       "      <th>自检全检结束时间</th>\n",
       "      <th>PDF处理开始时间</th>\n",
       "      <th>PDF处理结束时间</th>\n",
       "      <th>存在提前上岗</th>\n",
       "      <th>存在推迟下岗</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>托629441-册一</td>\n",
       "      <td>2020-07-01 09:00:48.250</td>\n",
       "      <td>2020-07-01 10:10:39</td>\n",
       "      <td>2020-07-01 14:55:27</td>\n",
       "      <td>2020-07-01 15:17:26</td>\n",
       "      <td>2020-07-28 14:21:45</td>\n",
       "      <td>2020-07-28 15:00:26</td>\n",
       "      <td>2020-07-29 12:55:57</td>\n",
       "      <td>2020-07-29 13:09:57</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>托629442-册一</td>\n",
       "      <td>2020-07-01 09:00:48.287</td>\n",
       "      <td>2020-07-01 10:10:41</td>\n",
       "      <td>2020-07-01 14:55:27</td>\n",
       "      <td>2020-07-01 15:18:45</td>\n",
       "      <td>2020-07-28 14:21:45</td>\n",
       "      <td>2020-07-28 15:31:03</td>\n",
       "      <td>2020-07-29 12:55:57</td>\n",
       "      <td>2020-07-29 13:09:57</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>托629443-册一</td>\n",
       "      <td>2020-07-01 09:00:48.297</td>\n",
       "      <td>2020-07-01 10:10:43</td>\n",
       "      <td>2020-07-01 14:55:27</td>\n",
       "      <td>2020-07-01 15:21:01</td>\n",
       "      <td>2020-07-28 14:21:45</td>\n",
       "      <td>2020-07-28 16:50:12</td>\n",
       "      <td>2020-07-29 12:55:57</td>\n",
       "      <td>2020-07-29 13:09:57</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>托629444-册一</td>\n",
       "      <td>2020-07-01 09:00:48.303</td>\n",
       "      <td>2020-07-01 10:10:47</td>\n",
       "      <td>2020-07-01 14:55:27</td>\n",
       "      <td>2020-07-01 15:24:01</td>\n",
       "      <td>2020-07-28 14:21:45</td>\n",
       "      <td>2020-07-28 15:12:08</td>\n",
       "      <td>2020-07-29 12:55:57</td>\n",
       "      <td>2020-07-29 13:09:57</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>托629445-册一</td>\n",
       "      <td>2020-07-01 09:00:48.310</td>\n",
       "      <td>2020-07-01 10:10:48</td>\n",
       "      <td>2020-07-01 14:55:27</td>\n",
       "      <td>2020-07-01 15:25:24</td>\n",
       "      <td>2020-07-28 14:21:45</td>\n",
       "      <td>2020-07-28 15:13:59</td>\n",
       "      <td>2020-07-29 12:55:57</td>\n",
       "      <td>2020-07-29 13:09:57</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33954</th>\n",
       "      <td>托698747-册一</td>\n",
       "      <td>2020-07-30 13:10:03.750</td>\n",
       "      <td>2020-07-30 16:18:13</td>\n",
       "      <td>2020-07-30 16:19:40</td>\n",
       "      <td>2020-07-30 16:29:07</td>\n",
       "      <td>2020-07-31 08:27:52</td>\n",
       "      <td>2020-07-31 10:09:18</td>\n",
       "      <td>2020-07-31 11:07:01</td>\n",
       "      <td>2020-07-31 14:07:29</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33955</th>\n",
       "      <td>托698748-册一</td>\n",
       "      <td>2020-07-30 13:10:03.760</td>\n",
       "      <td>2020-07-30 16:18:15</td>\n",
       "      <td>2020-07-30 16:19:40</td>\n",
       "      <td>2020-07-30 16:30:38</td>\n",
       "      <td>2020-07-31 08:27:52</td>\n",
       "      <td>2020-07-31 10:09:20</td>\n",
       "      <td>2020-07-31 11:07:01</td>\n",
       "      <td>2020-07-31 11:21:18</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33956</th>\n",
       "      <td>托698749-册一</td>\n",
       "      <td>2020-07-30 13:10:03.770</td>\n",
       "      <td>2020-07-30 16:18:17</td>\n",
       "      <td>2020-07-30 16:19:40</td>\n",
       "      <td>2020-07-30 16:31:41</td>\n",
       "      <td>2020-07-31 08:27:52</td>\n",
       "      <td>2020-07-31 10:09:22</td>\n",
       "      <td>2020-07-31 11:07:01</td>\n",
       "      <td>2020-07-31 11:21:18</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33957</th>\n",
       "      <td>托698750-册一</td>\n",
       "      <td>2020-07-30 13:10:03.777</td>\n",
       "      <td>2020-07-30 16:18:21</td>\n",
       "      <td>2020-07-30 16:19:40</td>\n",
       "      <td>2020-07-30 16:32:40</td>\n",
       "      <td>2020-07-31 08:27:52</td>\n",
       "      <td>2020-07-31 10:09:23</td>\n",
       "      <td>2020-07-31 11:07:01</td>\n",
       "      <td>2020-07-31 11:21:19</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33982</th>\n",
       "      <td>托698742-册一</td>\n",
       "      <td>2020-07-30 13:10:03.703</td>\n",
       "      <td>2020-07-30 16:22:44</td>\n",
       "      <td>2020-07-30 16:19:40</td>\n",
       "      <td>2020-07-30 16:22:29</td>\n",
       "      <td>2020-07-31 08:27:52</td>\n",
       "      <td>2020-07-31 10:09:09</td>\n",
       "      <td>2020-07-31 11:07:01</td>\n",
       "      <td>2020-07-31 11:21:18</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8561 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         sARCH_ID                  扫描开始时间              扫描结束时间  \\\n",
       "17     托629441-册一 2020-07-01 09:00:48.250 2020-07-01 10:10:39   \n",
       "18     托629442-册一 2020-07-01 09:00:48.287 2020-07-01 10:10:41   \n",
       "19     托629443-册一 2020-07-01 09:00:48.297 2020-07-01 10:10:43   \n",
       "20     托629444-册一 2020-07-01 09:00:48.303 2020-07-01 10:10:47   \n",
       "21     托629445-册一 2020-07-01 09:00:48.310 2020-07-01 10:10:48   \n",
       "...           ...                     ...                 ...   \n",
       "33954  托698747-册一 2020-07-30 13:10:03.750 2020-07-30 16:18:13   \n",
       "33955  托698748-册一 2020-07-30 13:10:03.760 2020-07-30 16:18:15   \n",
       "33956  托698749-册一 2020-07-30 13:10:03.770 2020-07-30 16:18:17   \n",
       "33957  托698750-册一 2020-07-30 13:10:03.777 2020-07-30 16:18:21   \n",
       "33982  托698742-册一 2020-07-30 13:10:03.703 2020-07-30 16:22:44   \n",
       "\n",
       "                 图像处理开始时间            图像处理结束时间            自检全检开始时间  \\\n",
       "17    2020-07-01 14:55:27 2020-07-01 15:17:26 2020-07-28 14:21:45   \n",
       "18    2020-07-01 14:55:27 2020-07-01 15:18:45 2020-07-28 14:21:45   \n",
       "19    2020-07-01 14:55:27 2020-07-01 15:21:01 2020-07-28 14:21:45   \n",
       "20    2020-07-01 14:55:27 2020-07-01 15:24:01 2020-07-28 14:21:45   \n",
       "21    2020-07-01 14:55:27 2020-07-01 15:25:24 2020-07-28 14:21:45   \n",
       "...                   ...                 ...                 ...   \n",
       "33954 2020-07-30 16:19:40 2020-07-30 16:29:07 2020-07-31 08:27:52   \n",
       "33955 2020-07-30 16:19:40 2020-07-30 16:30:38 2020-07-31 08:27:52   \n",
       "33956 2020-07-30 16:19:40 2020-07-30 16:31:41 2020-07-31 08:27:52   \n",
       "33957 2020-07-30 16:19:40 2020-07-30 16:32:40 2020-07-31 08:27:52   \n",
       "33982 2020-07-30 16:19:40 2020-07-30 16:22:29 2020-07-31 08:27:52   \n",
       "\n",
       "                 自检全检结束时间           PDF处理开始时间           PDF处理结束时间  存在提前上岗  \\\n",
       "17    2020-07-28 15:00:26 2020-07-29 12:55:57 2020-07-29 13:09:57    True   \n",
       "18    2020-07-28 15:31:03 2020-07-29 12:55:57 2020-07-29 13:09:57    True   \n",
       "19    2020-07-28 16:50:12 2020-07-29 12:55:57 2020-07-29 13:09:57    True   \n",
       "20    2020-07-28 15:12:08 2020-07-29 12:55:57 2020-07-29 13:09:57    True   \n",
       "21    2020-07-28 15:13:59 2020-07-29 12:55:57 2020-07-29 13:09:57    True   \n",
       "...                   ...                 ...                 ...     ...   \n",
       "33954 2020-07-31 10:09:18 2020-07-31 11:07:01 2020-07-31 14:07:29    True   \n",
       "33955 2020-07-31 10:09:20 2020-07-31 11:07:01 2020-07-31 11:21:18    True   \n",
       "33956 2020-07-31 10:09:22 2020-07-31 11:07:01 2020-07-31 11:21:18    True   \n",
       "33957 2020-07-31 10:09:23 2020-07-31 11:07:01 2020-07-31 11:21:19    True   \n",
       "33982 2020-07-31 10:09:09 2020-07-31 11:07:01 2020-07-31 11:21:18    True   \n",
       "\n",
       "       存在推迟下岗  \n",
       "17      False  \n",
       "18      False  \n",
       "19      False  \n",
       "20      False  \n",
       "21      False  \n",
       "...       ...  \n",
       "33954   False  \n",
       "33955   False  \n",
       "33956   False  \n",
       "33957   False  \n",
       "33982   False  \n",
       "\n",
       "[8561 rows x 11 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final[df_final['存在提前上岗']==True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a9c87ba5d91f74ff",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:07:46.603456100Z",
     "start_time": "2023-11-11T02:07:46.576455200Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "## 计算完成时长。这里假设完成时长是各个工序的结束时间的最大值减去开始时间的最小值。\n",
    "df_final['完成时长'] = df_final[['扫描结束时间', '图像处理结束时间', '自检全检结束时间', 'PDF处理结束时间']].max(axis=1) - df_final[['扫描开始时间', '图像处理开始时间', '自检全检开始时间', 'PDF处理开始时间']].min(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ab86d1c2a0e84297",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:07:50.534598600Z",
     "start_time": "2023-11-11T02:07:50.515599700Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sARCH_ID</th>\n",
       "      <th>扫描开始时间</th>\n",
       "      <th>扫描结束时间</th>\n",
       "      <th>图像处理开始时间</th>\n",
       "      <th>图像处理结束时间</th>\n",
       "      <th>自检全检开始时间</th>\n",
       "      <th>自检全检结束时间</th>\n",
       "      <th>PDF处理开始时间</th>\n",
       "      <th>PDF处理结束时间</th>\n",
       "      <th>存在提前上岗</th>\n",
       "      <th>存在推迟下岗</th>\n",
       "      <th>完成时长</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>托644031-册一</td>\n",
       "      <td>2020-07-01 09:17:22.313</td>\n",
       "      <td>2020-07-01 09:21:10</td>\n",
       "      <td>2020-07-01 15:01:56</td>\n",
       "      <td>2020-07-01 16:44:24</td>\n",
       "      <td>2020-07-08 08:35:26</td>\n",
       "      <td>2020-07-08 10:54:15</td>\n",
       "      <td>2020-07-10 15:05:18</td>\n",
       "      <td>2020-07-10 15:18:02</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>9 days 06:00:39.687000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>托644032-册一</td>\n",
       "      <td>2020-07-01 09:17:42.867</td>\n",
       "      <td>2020-07-01 09:21:17</td>\n",
       "      <td>2020-07-01 15:01:56</td>\n",
       "      <td>2020-07-01 16:44:26</td>\n",
       "      <td>2020-07-08 08:35:26</td>\n",
       "      <td>2020-07-08 10:54:17</td>\n",
       "      <td>2020-07-10 15:05:18</td>\n",
       "      <td>2020-07-10 15:18:02</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>9 days 06:00:19.133000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>托7181-册一</td>\n",
       "      <td>2020-07-01 08:39:08.370</td>\n",
       "      <td>2020-07-01 09:26:29</td>\n",
       "      <td>2020-07-02 10:47:50</td>\n",
       "      <td>2020-07-02 11:30:47</td>\n",
       "      <td>2020-07-02 11:31:18</td>\n",
       "      <td>2020-07-02 11:43:48</td>\n",
       "      <td>2020-07-03 08:49:13</td>\n",
       "      <td>2020-07-03 09:04:24</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2 days 00:25:15.630000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>托7182-册一</td>\n",
       "      <td>2020-07-01 08:39:08.397</td>\n",
       "      <td>2020-07-01 09:26:36</td>\n",
       "      <td>2020-07-02 10:47:50</td>\n",
       "      <td>2020-07-02 11:31:08</td>\n",
       "      <td>2020-07-02 11:31:18</td>\n",
       "      <td>2020-07-02 11:43:50</td>\n",
       "      <td>2020-07-03 08:49:13</td>\n",
       "      <td>2020-07-03 09:04:24</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2 days 00:25:15.603000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>托7183-册一</td>\n",
       "      <td>2020-07-01 08:39:08.420</td>\n",
       "      <td>2020-07-01 09:26:42</td>\n",
       "      <td>2020-07-02 13:37:15</td>\n",
       "      <td>2020-07-02 13:53:05</td>\n",
       "      <td>2020-07-02 15:07:37</td>\n",
       "      <td>2020-07-02 15:15:12</td>\n",
       "      <td>2020-07-03 08:49:13</td>\n",
       "      <td>2020-07-03 09:04:24</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2 days 00:25:15.580000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33980</th>\n",
       "      <td>托750599-册一</td>\n",
       "      <td>2020-07-30 15:27:57.460</td>\n",
       "      <td>2020-07-30 16:22:13</td>\n",
       "      <td>2020-07-31 10:10:06</td>\n",
       "      <td>2020-07-31 10:21:15</td>\n",
       "      <td>2020-07-31 10:23:55</td>\n",
       "      <td>2020-07-31 13:35:57</td>\n",
       "      <td>2020-07-31 14:06:17</td>\n",
       "      <td>2020-07-31 14:28:47</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0 days 23:00:49.540000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33981</th>\n",
       "      <td>托750600-册一</td>\n",
       "      <td>2020-07-30 15:28:27.477</td>\n",
       "      <td>2020-07-30 16:22:20</td>\n",
       "      <td>2020-07-30 16:52:21</td>\n",
       "      <td>2020-07-31 08:38:13</td>\n",
       "      <td>2020-07-31 10:24:12</td>\n",
       "      <td>2020-07-31 13:36:06</td>\n",
       "      <td>2020-07-31 14:06:17</td>\n",
       "      <td>2020-07-31 14:28:47</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0 days 23:00:19.523000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33982</th>\n",
       "      <td>托698742-册一</td>\n",
       "      <td>2020-07-30 13:10:03.703</td>\n",
       "      <td>2020-07-30 16:22:44</td>\n",
       "      <td>2020-07-30 16:19:40</td>\n",
       "      <td>2020-07-30 16:22:29</td>\n",
       "      <td>2020-07-31 08:27:52</td>\n",
       "      <td>2020-07-31 10:09:09</td>\n",
       "      <td>2020-07-31 11:07:01</td>\n",
       "      <td>2020-07-31 11:21:18</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>0 days 22:11:14.297000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33983</th>\n",
       "      <td>托750590-册一</td>\n",
       "      <td>2020-07-30 15:27:57.380</td>\n",
       "      <td>2020-07-30 16:25:02</td>\n",
       "      <td>2020-07-31 10:10:05</td>\n",
       "      <td>2020-07-31 10:20:51</td>\n",
       "      <td>2020-07-31 10:23:54</td>\n",
       "      <td>2020-07-31 13:35:31</td>\n",
       "      <td>2020-07-31 14:06:17</td>\n",
       "      <td>2020-07-31 14:28:46</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0 days 23:00:48.620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33984</th>\n",
       "      <td>托750591-册一</td>\n",
       "      <td>2020-07-30 15:27:57.390</td>\n",
       "      <td>2020-07-30 16:27:27</td>\n",
       "      <td>2020-07-31 10:10:05</td>\n",
       "      <td>2020-07-31 10:20:52</td>\n",
       "      <td>2020-07-31 10:23:54</td>\n",
       "      <td>2020-07-31 13:35:32</td>\n",
       "      <td>2020-07-31 14:06:17</td>\n",
       "      <td>2020-07-31 14:28:46</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0 days 23:00:48.610000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>33980 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         sARCH_ID                  扫描开始时间              扫描结束时间  \\\n",
       "0      托644031-册一 2020-07-01 09:17:22.313 2020-07-01 09:21:10   \n",
       "1      托644032-册一 2020-07-01 09:17:42.867 2020-07-01 09:21:17   \n",
       "2        托7181-册一 2020-07-01 08:39:08.370 2020-07-01 09:26:29   \n",
       "3        托7182-册一 2020-07-01 08:39:08.397 2020-07-01 09:26:36   \n",
       "4        托7183-册一 2020-07-01 08:39:08.420 2020-07-01 09:26:42   \n",
       "...           ...                     ...                 ...   \n",
       "33980  托750599-册一 2020-07-30 15:27:57.460 2020-07-30 16:22:13   \n",
       "33981  托750600-册一 2020-07-30 15:28:27.477 2020-07-30 16:22:20   \n",
       "33982  托698742-册一 2020-07-30 13:10:03.703 2020-07-30 16:22:44   \n",
       "33983  托750590-册一 2020-07-30 15:27:57.380 2020-07-30 16:25:02   \n",
       "33984  托750591-册一 2020-07-30 15:27:57.390 2020-07-30 16:27:27   \n",
       "\n",
       "                 图像处理开始时间            图像处理结束时间            自检全检开始时间  \\\n",
       "0     2020-07-01 15:01:56 2020-07-01 16:44:24 2020-07-08 08:35:26   \n",
       "1     2020-07-01 15:01:56 2020-07-01 16:44:26 2020-07-08 08:35:26   \n",
       "2     2020-07-02 10:47:50 2020-07-02 11:30:47 2020-07-02 11:31:18   \n",
       "3     2020-07-02 10:47:50 2020-07-02 11:31:08 2020-07-02 11:31:18   \n",
       "4     2020-07-02 13:37:15 2020-07-02 13:53:05 2020-07-02 15:07:37   \n",
       "...                   ...                 ...                 ...   \n",
       "33980 2020-07-31 10:10:06 2020-07-31 10:21:15 2020-07-31 10:23:55   \n",
       "33981 2020-07-30 16:52:21 2020-07-31 08:38:13 2020-07-31 10:24:12   \n",
       "33982 2020-07-30 16:19:40 2020-07-30 16:22:29 2020-07-31 08:27:52   \n",
       "33983 2020-07-31 10:10:05 2020-07-31 10:20:51 2020-07-31 10:23:54   \n",
       "33984 2020-07-31 10:10:05 2020-07-31 10:20:52 2020-07-31 10:23:54   \n",
       "\n",
       "                 自检全检结束时间           PDF处理开始时间           PDF处理结束时间  存在提前上岗  \\\n",
       "0     2020-07-08 10:54:15 2020-07-10 15:05:18 2020-07-10 15:18:02   False   \n",
       "1     2020-07-08 10:54:17 2020-07-10 15:05:18 2020-07-10 15:18:02   False   \n",
       "2     2020-07-02 11:43:48 2020-07-03 08:49:13 2020-07-03 09:04:24   False   \n",
       "3     2020-07-02 11:43:50 2020-07-03 08:49:13 2020-07-03 09:04:24   False   \n",
       "4     2020-07-02 15:15:12 2020-07-03 08:49:13 2020-07-03 09:04:24   False   \n",
       "...                   ...                 ...                 ...     ...   \n",
       "33980 2020-07-31 13:35:57 2020-07-31 14:06:17 2020-07-31 14:28:47   False   \n",
       "33981 2020-07-31 13:36:06 2020-07-31 14:06:17 2020-07-31 14:28:47   False   \n",
       "33982 2020-07-31 10:09:09 2020-07-31 11:07:01 2020-07-31 11:21:18    True   \n",
       "33983 2020-07-31 13:35:31 2020-07-31 14:06:17 2020-07-31 14:28:46   False   \n",
       "33984 2020-07-31 13:35:32 2020-07-31 14:06:17 2020-07-31 14:28:46   False   \n",
       "\n",
       "       存在推迟下岗                   完成时长  \n",
       "0       False 9 days 06:00:39.687000  \n",
       "1       False 9 days 06:00:19.133000  \n",
       "2       False 2 days 00:25:15.630000  \n",
       "3       False 2 days 00:25:15.603000  \n",
       "4       False 2 days 00:25:15.580000  \n",
       "...       ...                    ...  \n",
       "33980   False 0 days 23:00:49.540000  \n",
       "33981   False 0 days 23:00:19.523000  \n",
       "33982   False 0 days 22:11:14.297000  \n",
       "33983   False 0 days 23:00:48.620000  \n",
       "33984   False 0 days 23:00:48.610000  \n",
       "\n",
       "[33980 rows x 12 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d122c223231d37c0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:10:24.988720600Z",
     "start_time": "2023-11-11T02:10:24.975916700Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_result = df_final[['sARCH_ID', '扫描开始时间', '扫描结束时间', '图像处理开始时间', '图像处理结束时间', '自检全检开始时间', '自检全检结束时间', 'PDF处理开始时间', 'PDF处理结束时间', '完成时长']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "2bf22dbadca1f90e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:13:12.283694900Z",
     "start_time": "2023-11-11T02:13:05.292504500Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "ename": "OSError",
     "evalue": "Cannot save file into a non-existent directory: 'D:\\A题-档案数字化加工流程数据分析\result'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mOSError\u001b[0m                                   Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_8688\\1600397436.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf_result\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_excel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'D:\\A题-档案数字化加工流程数据分析\\result\\\\result1_1.xlsx'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mD:\\python\\Anaconda\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36mto_excel\u001b[1;34m(self, excel_writer, sheet_name, na_rep, float_format, columns, header, index, index_label, startrow, startcol, engine, merge_cells, encoding, inf_rep, verbose, freeze_panes, storage_options)\u001b[0m\n\u001b[0;32m   2343\u001b[0m             \u001b[0minf_rep\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0minf_rep\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2344\u001b[0m         )\n\u001b[1;32m-> 2345\u001b[1;33m         formatter.write(\n\u001b[0m\u001b[0;32m   2346\u001b[0m             \u001b[0mexcel_writer\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2347\u001b[0m             \u001b[0msheet_name\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0msheet_name\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\python\\Anaconda\\lib\\site-packages\\pandas\\io\\formats\\excel.py\u001b[0m in \u001b[0;36mwrite\u001b[1;34m(self, writer, sheet_name, startrow, startcol, freeze_panes, engine, storage_options)\u001b[0m\n\u001b[0;32m    886\u001b[0m             \u001b[1;31m# error: Cannot instantiate abstract class 'ExcelWriter' with abstract\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    887\u001b[0m             \u001b[1;31m# attributes 'engine', 'save', 'supported_extensions' and 'write_cells'\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 888\u001b[1;33m             writer = ExcelWriter(  # type: ignore[abstract]\n\u001b[0m\u001b[0;32m    889\u001b[0m                 \u001b[0mwriter\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstorage_options\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mstorage_options\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    890\u001b[0m             )\n",
      "\u001b[1;32mD:\\python\\Anaconda\\lib\\site-packages\\pandas\\io\\excel\\_xlsxwriter.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, path, engine, date_format, datetime_format, mode, storage_options, if_sheet_exists, engine_kwargs, **kwargs)\u001b[0m\n\u001b[0;32m    189\u001b[0m             \u001b[1;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"Append mode is not supported with xlsxwriter!\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    190\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 191\u001b[1;33m         super().__init__(\n\u001b[0m\u001b[0;32m    192\u001b[0m             \u001b[0mpath\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    193\u001b[0m             \u001b[0mengine\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\python\\Anaconda\\lib\\site-packages\\pandas\\io\\excel\\_base.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, path, engine, date_format, datetime_format, mode, storage_options, if_sheet_exists, engine_kwargs, **kwargs)\u001b[0m\n\u001b[0;32m   1104\u001b[0m         )\n\u001b[0;32m   1105\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpath\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mExcelWriter\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1106\u001b[1;33m             self.handles = get_handle(\n\u001b[0m\u001b[0;32m   1107\u001b[0m                 \u001b[0mpath\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmode\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstorage_options\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mstorage_options\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mis_text\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1108\u001b[0m             )\n",
      "\u001b[1;32mD:\\python\\Anaconda\\lib\\site-packages\\pandas\\io\\common.py\u001b[0m in \u001b[0;36mget_handle\u001b[1;34m(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)\u001b[0m\n\u001b[0;32m    692\u001b[0m     \u001b[1;31m# Only for write methods\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    693\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[1;34m\"r\"\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mmode\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mis_path\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 694\u001b[1;33m         \u001b[0mcheck_parent_directory\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mhandle\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    695\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    696\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mcompression\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\python\\Anaconda\\lib\\site-packages\\pandas\\io\\common.py\u001b[0m in \u001b[0;36mcheck_parent_directory\u001b[1;34m(path)\u001b[0m\n\u001b[0;32m    566\u001b[0m     \u001b[0mparent\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mPath\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpath\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mparent\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    567\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mparent\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mis_dir\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 568\u001b[1;33m         \u001b[1;32mraise\u001b[0m \u001b[0mOSError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34mrf\"Cannot save file into a non-existent directory: '{parent}'\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    569\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    570\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mOSError\u001b[0m: Cannot save file into a non-existent directory: 'D:\\A题-档案数字化加工流程数据分析\result'"
     ]
    }
   ],
   "source": [
    "df_result.to_excel('D:\\A题-档案数字化加工流程数据分析\\result\\\\result1_1.xlsx',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5ab6121485142ee",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:13:23.238454700Z",
     "start_time": "2023-11-11T02:13:23.231380600Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79dd6236a0538b2c",
   "metadata": {},
   "source": [
    "# Task1.2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "39acebb98ef7dc08",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:33:27.785400900Z",
     "start_time": "2023-11-11T02:33:27.748399200Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.5862880682654112"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Task1.2\n",
    "\n",
    "## 筛选返工状态为5的行（需要返工且已提交的案卷）  \n",
    "df_rework = df[df['iNODE_STATUS'] == 5]\n",
    "\n",
    "## 完工案卷总数（状态为2或5）  \n",
    "total_completed = df[(df['iNODE_STATUS'] == 2) | (df['iNODE_STATUS'] == 5)].shape[0]\n",
    "\n",
    "## 需要返工的案卷数量  \n",
    "total_rework = df_rework.shape[0]\n",
    "\n",
    "## 计算百分比  \n",
    "percentage_rework = (total_rework / total_completed) * 100 if total_completed > 0 else 0\n",
    "\n",
    "percentage_rework"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "ce34d5419b12b20a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:33:31.031606400Z",
     "start_time": "2023-11-11T02:33:29.433191900Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 汇总返工情况  \n",
    "df_rework_summary = df_rework.groupby('sARCH_ID')['sNODE_NAME', 'dPROC_TIME'].agg('first').reset_index()\n",
    "\n",
    "# 将原数据透视，以案卷号为索引，工序节点名称为列名，工序结束时间为值  \n",
    "df_pivot = df.pivot(index='sARCH_ID', columns='sNODE_NAME', values='dNODE_TIME')\n",
    "\n",
    "# 将返工汇总数据与原数据透视表合并  \n",
    "df_result = pd.merge(df_pivot, df_rework_summary, on='sARCH_ID', how='left')\n",
    "\n",
    "# 调整列顺序并填充NaT（不是时间）为空格  \n",
    "df_result = df_result[['sARCH_ID', '扫描', '图像处理', '自检全检', 'PDF处理']].fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "51ea4500f8b071f6",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:33:33.300692100Z",
     "start_time": "2023-11-11T02:33:33.291691800Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_result.columns = ['案卷号', '扫描', '图像处理', '自检全检', 'PDF处理']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "9f455b8854bd7fb3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:33:34.136771400Z",
     "start_time": "2023-11-11T02:33:34.116671400Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>案卷号</th>\n",
       "      <th>扫描</th>\n",
       "      <th>图像处理</th>\n",
       "      <th>自检全检</th>\n",
       "      <th>PDF处理</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>托40606-册七</td>\n",
       "      <td>2020-07-07 10:21:45</td>\n",
       "      <td>2020-07-07 10:44:12</td>\n",
       "      <td>2020-07-07 11:05:11</td>\n",
       "      <td>2020-07-07 15:44:25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>托40606-册六</td>\n",
       "      <td>2020-07-07 10:26:14</td>\n",
       "      <td>2020-07-07 10:48:56</td>\n",
       "      <td>2020-07-07 11:04:22</td>\n",
       "      <td>2020-07-07 15:44:25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>托5901_1-册三</td>\n",
       "      <td>2020-07-11 10:17:38</td>\n",
       "      <td>2020-07-11 14:26:09</td>\n",
       "      <td>2020-07-13 11:02:48</td>\n",
       "      <td>2020-07-14 17:56:11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           案卷号                  扫描                图像处理                自检全检  \\\n",
       "8    托40606-册七 2020-07-07 10:21:45 2020-07-07 10:44:12 2020-07-07 11:05:11   \n",
       "13   托40606-册六 2020-07-07 10:26:14 2020-07-07 10:48:56 2020-07-07 11:04:22   \n",
       "22  托5901_1-册三 2020-07-11 10:17:38 2020-07-11 14:26:09 2020-07-13 11:02:48   \n",
       "\n",
       "                  PDF处理  \n",
       "8   2020-07-07 15:44:25  \n",
       "13  2020-07-07 15:44:25  \n",
       "22  2020-07-14 17:56:11  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file_flags = [\"托40606-册六\", \"托40606-册七\", \"托5901_1-册三\"]\n",
    "df_result.loc[df_result['案卷号'].isin(file_flags)]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "bd9c2edb66ddb72b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:33:40.298517500Z",
     "start_time": "2023-11-11T02:33:36.895745200Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_result.to_excel('../sub/result1_2.xlsx',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cdde0f825477a1b",
   "metadata": {},
   "source": [
    "# Task1.3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "efce58383f984b35",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:38:42.582409800Z",
     "start_time": "2023-11-11T02:38:42.553411300Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 筛选出自检全检工序的数据  \n",
    "inspection_data = df[df['iFLOW_NODE_NO'] == 3]\n",
    "\n",
    "# 汇总每个操作人员的返工案卷数  \n",
    "rework_count = inspection_data[inspection_data['iNODE_STATUS'] == 5].groupby('iUSER_ID')['uFILE_FLAG'].count()\n",
    "\n",
    "# 汇总每个操作人员的总案卷数  \n",
    "total_count = inspection_data.groupby('iUSER_ID')['uFILE_FLAG'].count()\n",
    "\n",
    "# 计算返工案卷占比  \n",
    "rework_percentage = (rework_count / total_count) * 100\n",
    "\n",
    "# 按百分比降序排列  \n",
    "sorted_percentage = rework_percentage.sort_values(ascending=False)\n",
    "\n",
    "# 保留3位小数  \n",
    "result = sorted_percentage.round(3)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "3b846d73a617769c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:39:53.263055600Z",
     "start_time": "2023-11-11T02:39:53.247018600Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "result.columns = ['操作人员ID', '返工案卷占比 (%)']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "7416b8540af9201a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:40:30.357095600Z",
     "start_time": "2023-11-11T02:40:30.336603700Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "result.to_excel('../sub/result1_3.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "6de3bf6e81ba75a3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:42:11.468057Z",
     "start_time": "2023-11-11T02:42:11.451481300Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "iUSER_ID\n",
       "17    4.348\n",
       "42    2.147\n",
       "12    1.319\n",
       "Name: uFILE_FLAG, dtype: float64"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 占比前三\n",
    "result[:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fc80403b57d1ffc",
   "metadata": {},
   "source": [
    "# Task1.4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "92887e4cc118c8c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:49:44.257478700Z",
     "start_time": "2023-11-11T02:49:44.245486Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def is_workday(date):\n",
    "    weekdays = [0, 1, 2, 3, 4,5]  # 周一到周六\n",
    "    return date.weekday() in weekdays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "48a7255db6e652c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:58:19.001139200Z",
     "start_time": "2023-11-11T02:57:50.947492600Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_excel('../data.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "11712b28cd092000",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:59:43.615726200Z",
     "start_time": "2023-11-11T02:59:39.729744400Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from datetime import datetime, timedelta\n",
    "# 转换时间为datetime类型  \n",
    "df['dUPDATE_TIME'] = pd.to_datetime(df['dUPDATE_TIME'])\n",
    "df['dNODE_TIME'] = pd.to_datetime(df['dNODE_TIME'])\n",
    "df['dPROC_TIME'] = pd.to_datetime(df['dPROC_TIME'])\n",
    "\n",
    "\n",
    "# 已完成并提交的案卷  \n",
    "df = df[(df['iNODE_STATUS'] == 2) | (df['iNODE_STATUS'] == 5)]\n",
    "\n",
    "# 计算每批的总耗时  \n",
    "df['Total_Time'] = df['dNODE_TIME'] - df['dUPDATE_TIME']\n",
    "\n",
    "# 计算每批中每个案卷的工作日时间  \n",
    "df['Workday_Time'] = df.apply(lambda row: row['Total_Time'] if is_workday(row['dUPDATE_TIME'].date()) and is_workday(row['dNODE_TIME'].date()) else timedelta(0), axis=1)\n",
    "\n",
    "# 将工作日时间转换为小时  \n",
    "df['Workday_Time_Hours'] = df['Workday_Time'].dt.total_seconds() / 3600\n",
    "\n",
    "# 根据工序分组计算完成案卷的数量、工作日总耗时和平均耗时  \n",
    "grouped = df.groupby('iFLOW_NODE_NO').agg({'iID': 'count', 'Workday_Time_Hours': 'sum', 'sBatch_number': 'first'}).reset_index()\n",
    "\n",
    "# 计算平均耗时  \n",
    "grouped['Avg_Time_Hours'] = grouped['Workday_Time_Hours'] / grouped['iID']\n",
    "\n",
    "\n",
    "grouped = grouped.drop('sBatch_number',axis=1)\n",
    "# 重命名列名  \n",
    "grouped.columns = ['工序', '完成案卷的数量', '总耗时 (h)', '平均耗时 (h/卷)']\n",
    "\n",
    "# 结果保留3位小数  \n",
    "grouped = grouped.round(3)\n",
    "\n",
    "\n",
    "# map\n",
    "map = {1:'扫描',2:'图像处理',3:'自检全检',4:'PDF处理'}\n",
    "\n",
    "grouped['工序'] = grouped['工序'].map(map)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "f7fcfd052c57e62f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T02:59:47.086051700Z",
     "start_time": "2023-11-11T02:59:47.059924600Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>工序</th>\n",
       "      <th>完成案卷的数量</th>\n",
       "      <th>总耗时 (h)</th>\n",
       "      <th>平均耗时 (h/卷)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>扫描</td>\n",
       "      <td>33985</td>\n",
       "      <td>433291.164</td>\n",
       "      <td>12.749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>图像处理</td>\n",
       "      <td>33985</td>\n",
       "      <td>193841.150</td>\n",
       "      <td>5.704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>自检全检</td>\n",
       "      <td>33985</td>\n",
       "      <td>154799.739</td>\n",
       "      <td>4.555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PDF处理</td>\n",
       "      <td>33985</td>\n",
       "      <td>51059.637</td>\n",
       "      <td>1.502</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      工序  完成案卷的数量     总耗时 (h)  平均耗时 (h/卷)\n",
       "0     扫描    33985  433291.164      12.749\n",
       "1   图像处理    33985  193841.150       5.704\n",
       "2   自检全检    33985  154799.739       4.555\n",
       "3  PDF处理    33985   51059.637       1.502"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "f9b3cc5df582fcf2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T03:04:23.985568300Z",
     "start_time": "2023-11-11T03:04:23.949575900Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "grouped.to_excel('../sub/result1_4.xlsx',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b5dbd34a97565a7",
   "metadata": {},
   "source": [
    "# Task1.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "79f4433485ee922a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T03:14:49.854004200Z",
     "start_time": "2023-11-11T03:14:23.040318Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 读取数据  \n",
    "df = pd.read_excel('../data.xlsx')\n",
    "\n",
    "# 转换时间格式为datetime  \n",
    "df['dUPDATE_TIME'] = pd.to_datetime(df['dUPDATE_TIME'])\n",
    "df['dNODE_TIME'] = pd.to_datetime(df['dNODE_TIME'])\n",
    "df['dPROC_TIME'] = pd.to_datetime(df['dPROC_TIME'])  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "d64cd871d9291f8a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T03:19:07.438190Z",
     "start_time": "2023-11-11T03:19:07.420191900Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 转换时间格式为datetime  \n",
    "df['dUPDATE_TIME'] = pd.to_datetime(df['dUPDATE_TIME'])\n",
    "df['dNODE_TIME'] = pd.to_datetime(df['dNODE_TIME'])\n",
    "df['dPROC_TIME'] = pd.to_datetime(df['dPROC_TIME'])\n",
    "\n",
    "\n",
    "# 修正工作时间\n",
    "df.loc[df['dUPDATE_TIME'].dt.hour < 8.5,'dUPDATE_TIME'] = df['dUPDATE_TIME'].dt.date + pd.Timedelta(hours=8)\n",
    "df['dUPDATE_TIME'] = pd.to_datetime(df['dUPDATE_TIME'])\n",
    "df.loc[(df['dUPDATE_TIME'].dt.hour >= 12) & (df['dUPDATE_TIME'].dt.hour < 13),'dUPDATE_TIME'] = df['dUPDATE_TIME'].dt.date + pd.Timedelta(hours=13)\n",
    "\n",
    "df.loc[(df['dNODE_TIME'].dt.hour > 12) & (df['dNODE_TIME'].dt.hour < 13),'dNODE_TIME'] = df['dNODE_TIME'].dt.date + pd.Timedelta(hours=12)\n",
    "df.loc[df['dNODE_TIME'].dt.hour > 18,'dNODE_TIME'] = df['dNODE_TIME'].dt.date + pd.Timedelta(hours=18)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "182f31e3fd66226",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T03:20:27.090843800Z",
     "start_time": "2023-11-11T03:20:27.077844900Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['dUPDATE_TIME'] = pd.to_datetime(df['dUPDATE_TIME'])\n",
    "df['dNODE_TIME'] = pd.to_datetime(df['dNODE_TIME'])\n",
    "df['dPROC_TIME'] = pd.to_datetime(df['dPROC_TIME'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "a0041f5aaa24a501",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T03:27:58.932472100Z",
     "start_time": "2023-11-11T03:27:58.914469200Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 计算工作时长、完成案卷的数量和每个案卷的平均耗时  \n",
    "grouped = df.groupby(['iUSER_ID', 'iFLOW_NODE_NO', 'sBatch_number'])\n",
    "result = grouped.agg({'iID':'count', 'dUPDATE_TIME':'first', 'dNODE_TIME':'last'})\n",
    "result.columns = ['Completed_Cases', 'Batch_Start_Time', 'Batch_End_Time']\n",
    "result['Batch_Duration'] = (result['Batch_End_Time'] - result['Batch_Start_Time']).dt.total_seconds()/3600  # in hours  \n",
    "result['Avg_Duration_Per_Case'] = result['Batch_Duration'] / result['Completed_Cases']  # in hours/case  \n",
    "result.reset_index(inplace=True)\n",
    "# 按操作人员ID升序排列  \n",
    "result.sort_values('iUSER_ID', inplace=True)\n",
    "\n",
    "# 删除多余的列  \n",
    "del result['sBatch_number']\n",
    "del result['Batch_Start_Time']\n",
    "\n",
    "# 修改列名为中文  \n",
    "result.rename(columns={'iUSER_ID': '操作人员ID', 'iFLOW_NODE_NO': '工序', 'Batch_Duration': '工作时长(h)', 'Completed_Cases': '完成案卷的数量', 'Avg_Duration_Per_Case': '每个案卷的平均耗时(h/卷)'}, inplace=True)\n",
    "\n",
    "# 工序列的映射  \n",
    "mapping = {1: '扫描', 2: '图像处理', 3: '自检全检', 4: 'PDF处理'}\n",
    "result['工序'] = result['工序'].map(mapping)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "2ca7f61a7278df0f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T03:29:38.005195400Z",
     "start_time": "2023-11-11T03:29:37.991201400Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>操作人员ID</th>\n",
       "      <th>工序</th>\n",
       "      <th>完成案卷的数量</th>\n",
       "      <th>工作时长(h)</th>\n",
       "      <th>每个案卷的平均耗时(h/卷)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>1</td>\n",
       "      <td>0.068611</td>\n",
       "      <td>0.068611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>1</td>\n",
       "      <td>1.675000</td>\n",
       "      <td>1.675000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>1</td>\n",
       "      <td>0.889167</td>\n",
       "      <td>0.889167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>1</td>\n",
       "      <td>0.280556</td>\n",
       "      <td>0.280556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>1</td>\n",
       "      <td>40.223611</td>\n",
       "      <td>40.223611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>95</td>\n",
       "      <td>扫描</td>\n",
       "      <td>20</td>\n",
       "      <td>17.357222</td>\n",
       "      <td>0.867861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>95</td>\n",
       "      <td>图像处理</td>\n",
       "      <td>3</td>\n",
       "      <td>1.663333</td>\n",
       "      <td>0.554444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>95</td>\n",
       "      <td>图像处理</td>\n",
       "      <td>8</td>\n",
       "      <td>22.077222</td>\n",
       "      <td>2.759653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>98</td>\n",
       "      <td>图像处理</td>\n",
       "      <td>1</td>\n",
       "      <td>15.900000</td>\n",
       "      <td>15.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>98</td>\n",
       "      <td>图像处理</td>\n",
       "      <td>1</td>\n",
       "      <td>1.399444</td>\n",
       "      <td>1.399444</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>126 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     操作人员ID    工序  完成案卷的数量    工作时长(h)  每个案卷的平均耗时(h/卷)\n",
       "0        10  自检全检        1   0.068611        0.068611\n",
       "16       10  自检全检        1   1.675000        1.675000\n",
       "15       10  自检全检        1   0.889167        0.889167\n",
       "14       10  自检全检        1   0.280556        0.280556\n",
       "13       10  自检全检        1  40.223611       40.223611\n",
       "..      ...   ...      ...        ...             ...\n",
       "121      95    扫描       20  17.357222        0.867861\n",
       "122      95  图像处理        3   1.663333        0.554444\n",
       "123      95  图像处理        8  22.077222        2.759653\n",
       "124      98  图像处理        1  15.900000       15.900000\n",
       "125      98  图像处理        1   1.399444        1.399444\n",
       "\n",
       "[126 rows x 5 columns]"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = result.drop('Batch_End_Time',axis=1)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "e37e0b5087dbd67a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T03:31:04.880709600Z",
     "start_time": "2023-11-11T03:31:04.870710600Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>操作人员ID</th>\n",
       "      <th>工序</th>\n",
       "      <th>完成案卷的数量</th>\n",
       "      <th>工作时长(h)</th>\n",
       "      <th>每个案卷的平均耗时(h/卷)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>1</td>\n",
       "      <td>0.068611</td>\n",
       "      <td>0.068611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>1</td>\n",
       "      <td>1.675000</td>\n",
       "      <td>1.675000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>1</td>\n",
       "      <td>0.889167</td>\n",
       "      <td>0.889167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>1</td>\n",
       "      <td>0.280556</td>\n",
       "      <td>0.280556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>1</td>\n",
       "      <td>40.223611</td>\n",
       "      <td>40.223611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>1</td>\n",
       "      <td>1.308889</td>\n",
       "      <td>1.308889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>3</td>\n",
       "      <td>1.175000</td>\n",
       "      <td>0.391667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>7</td>\n",
       "      <td>2.805278</td>\n",
       "      <td>0.400754</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>34</td>\n",
       "      <td>175.834167</td>\n",
       "      <td>5.171593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>7</td>\n",
       "      <td>0.977778</td>\n",
       "      <td>0.139683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>11</td>\n",
       "      <td>28.385556</td>\n",
       "      <td>2.580505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>1</td>\n",
       "      <td>14.754444</td>\n",
       "      <td>14.754444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>4</td>\n",
       "      <td>17.949167</td>\n",
       "      <td>4.487292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>2</td>\n",
       "      <td>19.388056</td>\n",
       "      <td>9.694028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>1</td>\n",
       "      <td>1.466667</td>\n",
       "      <td>1.466667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>1</td>\n",
       "      <td>0.826667</td>\n",
       "      <td>0.826667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10</td>\n",
       "      <td>自检全检</td>\n",
       "      <td>1</td>\n",
       "      <td>0.324722</td>\n",
       "      <td>0.324722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>33</td>\n",
       "      <td>扫描</td>\n",
       "      <td>4</td>\n",
       "      <td>17.173987</td>\n",
       "      <td>4.293497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>48</td>\n",
       "      <td>图像处理</td>\n",
       "      <td>1</td>\n",
       "      <td>15.895556</td>\n",
       "      <td>15.895556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>48</td>\n",
       "      <td>图像处理</td>\n",
       "      <td>1</td>\n",
       "      <td>0.237500</td>\n",
       "      <td>0.237500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>48</td>\n",
       "      <td>图像处理</td>\n",
       "      <td>3</td>\n",
       "      <td>14.031111</td>\n",
       "      <td>4.677037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>48</td>\n",
       "      <td>图像处理</td>\n",
       "      <td>27</td>\n",
       "      <td>17.154722</td>\n",
       "      <td>0.635360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>48</td>\n",
       "      <td>图像处理</td>\n",
       "      <td>1</td>\n",
       "      <td>1.025278</td>\n",
       "      <td>1.025278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>48</td>\n",
       "      <td>图像处理</td>\n",
       "      <td>20</td>\n",
       "      <td>17.091111</td>\n",
       "      <td>0.854556</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    操作人员ID    工序  完成案卷的数量     工作时长(h)  每个案卷的平均耗时(h/卷)\n",
       "0       10  自检全检        1    0.068611        0.068611\n",
       "16      10  自检全检        1    1.675000        1.675000\n",
       "15      10  自检全检        1    0.889167        0.889167\n",
       "14      10  自检全检        1    0.280556        0.280556\n",
       "13      10  自检全检        1   40.223611       40.223611\n",
       "11      10  自检全检        1    1.308889        1.308889\n",
       "10      10  自检全检        3    1.175000        0.391667\n",
       "9       10  自检全检        7    2.805278        0.400754\n",
       "12      10  自检全检       34  175.834167        5.171593\n",
       "7       10  自检全检        7    0.977778        0.139683\n",
       "6       10  自检全检       11   28.385556        2.580505\n",
       "5       10  自检全检        1   14.754444       14.754444\n",
       "4       10  自检全检        4   17.949167        4.487292\n",
       "3       10  自检全检        2   19.388056        9.694028\n",
       "2       10  自检全检        1    1.466667        1.466667\n",
       "1       10  自检全检        1    0.826667        0.826667\n",
       "8       10  自检全检        1    0.324722        0.324722\n",
       "61      33    扫描        4   17.173987        4.293497\n",
       "83      48  图像处理        1   15.895556       15.895556\n",
       "81      48  图像处理        1    0.237500        0.237500\n",
       "82      48  图像处理        3   14.031111        4.677037\n",
       "79      48  图像处理       27   17.154722        0.635360\n",
       "78      48  图像处理        1    1.025278        1.025278\n",
       "80      48  图像处理       20   17.091111        0.854556"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 操作人员ID列表  \n",
    "selected_user_ids = [10, 33, 48]  \n",
    "result[result['操作人员ID'].isin(selected_user_ids)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "dcb9fb670c425969",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-11T03:31:38.837414200Z",
     "start_time": "2023-11-11T03:31:38.780405300Z"
    },
    "jupyter": {
     "outputs_hidden": false
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "result.to_excel('../sub/result1_5.xlsx',index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
